package com.xiaotu.service.sync;

import cn.hutool.core.util.ObjectUtil;
import com.xiaotu.config.FileConfig;
import com.xiaotu.entity.*;
import com.xiaotu.service.mysql.MysqlSwIndexService;
import com.xiaotu.service.mysql8.Mysql8SwIndexService;
import com.xiaotu.service.pgsql.PgsqlXtIndexDataSourceService;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author zhangxiao
 * @create 2021-10-27 14:03
 */
@Service
public class SyncXtIndexService {

    public static final String SCREENWRITER_WORKS_TABLE_NAME = "basicinfo.tab_screenwriter_works_to_tencent";

    public static final String TAB_SW_HOT_TABLE_NAME = "screenwriter.tab_sw_hot";

    public static final String TAB_ACTOR_INDEX_TABLE_NAME = "screenwriter.tab_actor_index";


    @Autowired
    private PgsqlXtIndexDataSourceService pgsqlXtIndexDataSourceService;

    @Resource(name = "mysql8TransactionManager")
    private DataSourceTransactionManager mysql8TransactionManager;

    @Autowired
    private Mysql8SwIndexService mysql8SwIndexService;

    @Autowired
    private MysqlSwIndexService mysqlSwIndexService;

    @Transactional
    public void syncTvBasicData() throws Exception {
        runScript(FileConfig.ddl_tv_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `tv`(`starid`, `starname`, `tvid`, `tvname`, `firstplaytime`, `releasetime`, `startdate`, `starttime`, `endtime`, `rate`, `market`, `channel_name`) VALUES \n");
        List<XtIndexSyncTvModel> xtIndexSyncTv = pgsqlXtIndexDataSourceService.getXtIndexSyncTv(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncTvModel tv : xtIndexSyncTv) {
            sb.append("(" + tv.getStarid() + ",'" + tv.getStarname() + "'," + tv.getTvid() + ",'" + tv.getTvname() + "'");
            sb.append(ObjectUtil.isNull(tv.getFirstplaytime()) ? "," + tv.getFirstplaytime() : ",'" + tv.getFirstplaytime() + "'");
            sb.append(ObjectUtil.isNull(tv.getReleasetime()) ? "," + tv.getReleasetime() : ",'" + tv.getReleasetime() + "'");
            sb.append(ObjectUtil.isNull(tv.getStartdate()) ? "," + tv.getStartdate() : ",'" + tv.getStartdate() + "'");
            sb.append("," + tv.getStarttime());
            sb.append("," + tv.getEndtime());
            sb.append("," + tv.getRate());
            sb.append("," + tv.getMarket());
            sb.append(ObjectUtil.isNull(tv.getChannel_name()) ? "," + tv.getChannel_name() : ",'" + tv.getChannel_name() + "'");
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_tv_sql_path, sql);
        runScript(FileConfig.insert_tv_sql_path);
    }

    @Transactional
    public void syncMovieBasicData() throws Exception {
        runScript(FileConfig.ddl_movie_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `pf`(`starid`, `starname`, `tvid`, `tvname`, `firstplaytime`, `releasetime`, `totalboxoffice`) VALUES \n");
        List<XtIndexSyncMovieModel> xtIndexSyncMovie = pgsqlXtIndexDataSourceService.getXtIndexSyncMovie(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncMovieModel movie : xtIndexSyncMovie) {
            sb.append("(" + movie.getStarid() + ",'" + movie.getStarname() + "'," + movie.getTvid() + ",'" + movie.getTvname() + "'");
            sb.append(ObjectUtil.isNull(movie.getFirstplaytime()) ? "," + movie.getFirstplaytime() : ",'" + movie.getFirstplaytime() + "'");
            sb.append(ObjectUtil.isNull(movie.getReleasetime()) ? "," + movie.getReleasetime() : ",'" + movie.getReleasetime() + "'");
            sb.append("," + movie.getTotalboxoffice());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_movie_sql_path, sql);
        runScript(FileConfig.insert_movie_sql_path);
    }

    @Transactional
    public void syncNetTvBasicData() throws Exception {
        runScript(FileConfig.ddl_nettv_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `wlbfl`(`starid`, `starname`, `tvid`, `tvname`, `firstplaytime`, `releasetime`, `tencent`, `souhu`, `mgtv`, `iqy`, `youku`, `db_score`) VALUES \n");
        List<XtIndexSyncNetModel> xtIndexSyncNetTV = pgsqlXtIndexDataSourceService.getXtIndexSyncNetTV(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncNetModel netTv : xtIndexSyncNetTV) {
            sb.append("(" + netTv.getStarid() + ",'" + netTv.getStarname() + "'," + netTv.getTvid() + ",'" + netTv.getTvname() + "'");
            sb.append(ObjectUtil.isNull(netTv.getFirstplaytime()) ? "," + netTv.getFirstplaytime() : ",'" + netTv.getFirstplaytime() + "'");
            sb.append(ObjectUtil.isNull(netTv.getReleasetime()) ? "," + netTv.getReleasetime() : ",'" + netTv.getReleasetime() + "'");
            sb.append("," + netTv.getTencent());
            sb.append("," + netTv.getSouhu());
            sb.append("," + netTv.getMgtv());
            sb.append("," + netTv.getIqy());
            sb.append("," + netTv.getYouku());
            sb.append("," + netTv.getDb_score());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_nettv_sql_path, sql);
        runScript(FileConfig.insert_nettv_sql_path);
    }

    @Transactional
    public void syncNetTvHotBasicData() throws Exception {
        runScript(FileConfig.ddl_nettv_hot_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `wlbfl_hot`(`starid`, `starname`, `tvid`, `tvname`, `firstplaytime`, `releasetime`, `tencent`, `souhu`, `mgtv`, `iqy`, `youku`, `db_score`) VALUES \n");
        List<XtIndexSyncNetModel> xtIndexSyncNetTV = pgsqlXtIndexDataSourceService.getXtIndexSyncNetTV(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncNetModel netTv : xtIndexSyncNetTV) {
            sb.append("(" + netTv.getStarid() + ",'" + netTv.getStarname() + "'," + netTv.getTvid() + ",'" + netTv.getTvname() + "'");
            sb.append(ObjectUtil.isNull(netTv.getFirstplaytime()) ? "," + netTv.getFirstplaytime() : ",'" + netTv.getFirstplaytime() + "'");
            sb.append(ObjectUtil.isNull(netTv.getReleasetime()) ? "," + netTv.getReleasetime() : ",'" + netTv.getReleasetime() + "'");
            sb.append("," + netTv.getTencent());
            sb.append("," + netTv.getSouhu());
            sb.append("," + netTv.getMgtv());
            sb.append("," + netTv.getIqy());
            sb.append("," + netTv.getYouku());
            sb.append("," + netTv.getDb_score());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_nettv_hot_sql_path, sql);
        runScript(FileConfig.insert_nettv_hot_sql_path);
    }

    @Transactional
    public void syncNetMovieBasicData() throws Exception {
        runScript(FileConfig.ddl_netmovie_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `wlddy`(`starid`, `starname`, `tvid`, `tvname`, `firstplaytime`, `releasetime`, `tencent`, `souhu`, `mgtv`, `iqy`, `youku`, `db_score`) VALUES \n");
        List<XtIndexSyncNetModel> xtIndexSyncNetMovie = pgsqlXtIndexDataSourceService.getXtIndexSyncNetMovie(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncNetModel netMovie : xtIndexSyncNetMovie) {
            sb.append("(" + netMovie.getStarid() + ",'" + netMovie.getStarname() + "'," + netMovie.getTvid() + ",'" + netMovie.getTvname() + "'");
            sb.append(ObjectUtil.isNull(netMovie.getFirstplaytime()) ? "," + netMovie.getFirstplaytime() : ",'" + netMovie.getFirstplaytime() + "'");
            sb.append(ObjectUtil.isNull(netMovie.getReleasetime()) ? "," + netMovie.getReleasetime() : ",'" + netMovie.getReleasetime() + "'");
            sb.append("," + netMovie.getTencent());
            sb.append("," + netMovie.getSouhu());
            sb.append("," + netMovie.getMgtv());
            sb.append("," + netMovie.getIqy());
            sb.append("," + netMovie.getYouku());
            sb.append("," + netMovie.getDb_score());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_netmovie_sql_path, sql);
        runScript(FileConfig.insert_netmovie_sql_path);
    }

    @Transactional
    public void syncDouBanBasicData() throws Exception {
        runScript(FileConfig.ddl_douban_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `db`(`starid`, `starname`, `tvid`, `tvname`, `db_score`, `firstplaytime`) VALUES \n");
        List<XtIndexSyncDouBanModel> xtIndexSyncDouBan = pgsqlXtIndexDataSourceService.getXtIndexSyncDouBan(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncDouBanModel douban : xtIndexSyncDouBan) {
            sb.append("(" + douban.getStarid() + ",'" + douban.getStarname() + "'," + douban.getTvid() + ",'" + douban.getTvname() + "'");
            sb.append("," + douban.getDb_score());
            sb.append(ObjectUtil.isNull(douban.getFirstplaytime()) ? "," + douban.getFirstplaytime() : ",'" + douban.getFirstplaytime() + "'");
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_douban_sql_path, sql);
        runScript(FileConfig.insert_douban_sql_path);
    }

    @Transactional
    public void syncSwMovieWorksData() throws Exception {
        runScript(FileConfig.ddl_sw_movie_works_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `zpnum`(`starid`, `starname`, `zpnum`) VALUES \n");
        List<XtIndexSyncSwMovieWorksModel> xtIndexSyncSwMovieWorks = pgsqlXtIndexDataSourceService.getXtIndexSyncSwMovieWorks(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncSwMovieWorksModel swMovieWorks : xtIndexSyncSwMovieWorks) {
            sb.append("(" + swMovieWorks.getStarid() + ",'" + swMovieWorks.getStarname() + "'," + swMovieWorks.getZpnum());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_sw_movie_works_sql_path, sql);
        runScript(FileConfig.insert_sw_movie_works_sql_path);
    }

    @Transactional
    public void syncSwNetMovieWorksData() throws Exception {
        runScript(FileConfig.ddl_sw_netmovie_works_sql_path);
        StringBuffer sb = new StringBuffer("INSERT INTO `wdzpnum`(`starid`, `starname`, `zpnum`) VALUES \n");
        List<XtIndexSyncSwNetMovieWorksModel> xtIndexSyncSwNetMovieWorks = pgsqlXtIndexDataSourceService.getXtIndexSyncSwNetMovieWorks(SCREENWRITER_WORKS_TABLE_NAME);
        for (XtIndexSyncSwNetMovieWorksModel swNetMovieWorks : xtIndexSyncSwNetMovieWorks) {
            sb.append("(" + swNetMovieWorks.getStarid() + ",'" + swNetMovieWorks.getStarname() + "'," + swNetMovieWorks.getZpnum());
            sb.append("),\n");
        }
        String sql = sb.substring(0, sb.lastIndexOf(",")) + ";";
        createFile(FileConfig.insert_sw_netmovie_works_sql_path, sql);
        runScript(FileConfig.insert_sw_netmovie_works_sql_path);
    }

    /**
     * 编剧算法
     *
     * @throws SQLException
     */
    @Transactional
    public void screenwriterIindexAlgorithm() throws SQLException {
        runScript(FileConfig.ddl_screenwriter_index_algorithm_sql_path);
    }

    /**
     * 热播编剧算法
     *
     * @throws SQLException
     */
    @Transactional
    public void hotScreenwriterIindexAlgorithm() throws SQLException {
        runScript(FileConfig.ddl_hot_screenwriter_index_algorithm_sql_path);
    }

    /**
     * 同步编剧小土指数至 编剧数据库
     */
    @Transactional
    public void syncSwIndex() {
        List<SyncSwIndexModel> sources = mysql8SwIndexService.getSwIndex();
        mysqlSwIndexService.clearAll(TAB_ACTOR_INDEX_TABLE_NAME);
        mysqlSwIndexService.batchInsertSwIndex(sources);
    }

    /**
     * 同步热播编剧小土指数至 编剧数据库
     */
    @Transactional
    public void syncHotSwIndex() {
        List<SyncHotSwIndexModel> sources = mysql8SwIndexService.getHotSwIndex();
        for (int i = 0; i < sources.size(); i++) {
            sources.get(i).setOrderNum(i + 1);
        }
        mysqlSwIndexService.clearAll(TAB_SW_HOT_TABLE_NAME);
        mysqlSwIndexService.batchInsertHotSwIndex(sources);
    }

    private void runScript(String filePath) throws SQLException {
        Connection connection = mysql8TransactionManager.getDataSource().getConnection();
        ScriptRunner runner = new ScriptRunner(connection);
        FileInputStream is = null;
        InputStreamReader isr = null;
        try {
            is = new FileInputStream(new File(filePath));
            isr = new InputStreamReader(is, "UTF-8");
            runner.runScript(isr);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (ObjectUtil.isNotNull(is)) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (ObjectUtil.isNotNull(isr)) {
                try {
                    isr.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (ObjectUtil.isNotNull(connection))
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    /**
     * 创建文件并导出数据
     */
    private void createFile(String filePath, String sql) {
        File file = new File(filePath);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                System.out.println("创建文件名失败！！");
                e.printStackTrace();
            }
        }
        FileWriter fw = null;
        BufferedWriter bw = null;
        try {
            fw = new FileWriter(file);
            bw = new BufferedWriter(fw);
            bw.append(sql);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
                fw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}
